This invention relates to a software system, specifically to a system that automatically generates a presentation, document, or other formatted product in an application format for an end user based on a template and structured data.
Users of computer systems desire to see and share data with familiar user-friendly computer products, such as word processors, spreadsheets, databases and presentation software. Computer systems themselves often do not have the ability to produce products in such user-friendly formats. In the few systems that allow data to be exported to such user-friendly products, users have little control in customizing the results since formatting is usually hard coded within the system. Such hard coded solutions also result in support of only a single type of data structure. Thus, when the end user wishes to see and share data in a user-friendly product based on another of the system's data files, this new use must also be hard coded.
What is needed is a way to allow a computer end user to generate a slide show, document, spreadsheet or other presentation using a word processor or other user-friendly product, based (in part) upon structured data from a software system. Such an innovation should allow the end user to specify the layout of the presentation as well as how to organize the data records and which data records to include.
Traditional Mail Merge Functions and their Limitations
Microsoft WORD currently provides a mail merge helper feature. Variations of the mail merge feature are also available in other word processing software systems. The mail merge is designed to assist a user to create form letter documents and associated envelopes or mailing labels. As WORD's help file explains, mail merge feature guides the user through organizing the address data, merging it into a generic document, and printing the resulting personalized documents.
To allow for a comparison between current systems and the novel features offered by the present invention, the WORD mail merge helper feature will now be discussed at length, with the example of using the feature to generate form letters to a set of customers. As one in the art is aware, Microsoft Corporation produces the ubiquitous OFFICE suite of software products, which includes WORD for word processing, POWERPOINT for slide shows and other presentations, and EXCEL for spreadsheets. While the present document specifically discusses Microsoft products, the present invention may also be compared to, and implemented in connection with other computer-based applications available to end users. For example, end users may also enjoy the present invention (in various embodiments) with WordPerfect's office suite that includes the WORDPERFECT word processor, QUATTRO PRO spreadsheet, PRESENTATIONS slide show creator and the PARADOX database product. Or end users may choose to use software from other vendors, such as CRYSTAL REPORTS or the STAROFFICE suite from Sun Microsystems, to name but a few. Some embodiments of the present invention may support using web portlets (i.e., modules that can be plugged into a web portal for real-time data searching capabilities), exporting to web services in XML or other format, or leveraging the ODBC protocol for access to other types of data sources.
FIG. 1 shows the dialog box 10 for WORD's mail merge helper feature. The user may use the “Get Data” button 12 to associate a data file with the mail merge. FIG. 2 shows the EXCEL spreadsheet data file 20 that is being used as the data file in this example. Here, each column is given a field name and each row represents one data record. Thus, the second row contains the data for Steven Lieske and the third row contains the information for Mike Bennett. The mail merge feature can also use a database file, a text file or a table in a WORD document as the data file.
Once the data file has been set up, the user creates or edits the main document 14, which is the template for the merge. FIG. 3 shows a sample document created in WORD. The document includes generic text that will be included in each form letter, such as the sentence: “We are updating our computer records.” The document also contains a series of merge tags 32 and 34 that are used to personalize each form letter. The merge tags may be a placeholder to a data field or may be a simple processing instruction. FIG. 4 shows WORD's “Insert Merge Field” button 40, which allows the user to see all of the field names from the spreadsheet 20 and to choose which field names should be included in the template. In the example shown in FIG. 3, the fields 32 called NAME, ADDRESS, CITY, STATE and ZIP are all included in the template.
The mail merger helper feature also supports nine merge instructions that are accessed by the “Insert Word Field” button 50 shown in FIG. 5. The ASK and FILL-IN merge instructions can be inserted in a template to require the user to type in information from the keyboard as the merge is generating the form letters. With these two instructions, the user must be an active participant during the merge process since his or her keyboard input is required for each letter.
The IF-THEN-ELSE merge instruction provides conditional processing for text in the form letters. The template shown in FIG. 3 includes one of these IF-THEN-ELSE merge instructions 34. This instruction 34 tests the PHONE field for the record being used for each form letter. If the PHONE field is not blank (i.e., < > “ ”), then the text “Our records indicate your phone number is” is added to the form letter followed by the value of the present record's PHONE field. If, however, the PHONE field is blank, then the ‘else’ portion of the merge instruction causes the text “We do not have your phone number on file” to be added to the form letter.
The MERGE-RECORD-# merge instruction simply includes in the resulting form letter the number of the record being processed. For example, if the merge feature is using the fifth record's information to create the fifth form letter, then the number “5” is inserted where the MERGE-RECORD-# instruction is placed in the template.
In similar fashion, the MERGE-SEQUENCE-# merge instruction inserts in the form letter the total number of records being processed. For example, if the user decides to generate 10 form letters by processing records one through ten, then the number “10” is inserted where the MERGE-SEQUENCE-# instruction is placed in the template.
The NEXT-RECORD merge instruction causes the mail merge feature to proceed to the next record in the data file 20. This may allow a form letter to be a list reporting the data from several records. For example, the merged form letter may be set up to print the NAME and PHONE on a single page. The NEXT-RECORD-IF and SKIP-RECORD-IF merge instructions are similarly used to proceed to the next record on a single page, but these instructions provide a condition that must be met for either the next record to be processed or the next record to be skipped. The conditions allowed by these two merge instructions are similar to the IF portion of the IF-THEN-ELSE merge instruction discussed above.
The final merge instruction supported by WORD's mail merge helper is the SET-BOOKMARK merge instruction, which inserts the text associated to a specified bookmark.
Once the template has been created with the desired merge fields and word fields, the user may set up a query 16 to instruct which records should be included in the merge and how they should be sorted. FIG. 6 shows the FILTER RECORDS 60 dialog box that allows the user to select the criteria for inclusion in the form letters. Here, only records that have the STATE field set to MN will be used. FIG. 7 shows the SORT RECORDS 70 dialog box that allows the user to select the sorting rules for the form letters. Here, the records will be sorted by the NAME field.
Once the template is finished and the data file is selected, and once any filters or sorting requirements are set up, the user may generate the merged form letters 18. As the data file from FIG. 2 only includes two records with the STATE equal to MN, only two form letters are generated. FIG. 8 shows the results for the second form letter 80. Unfortunately, the chosen data file, the filters and the sorting requirements are all details that the user must set up each time the mail merge is processed. The data file name, the filters and the query itself may not be embedded within the template. What is needed is a way to embed such information into the template. Furthermore, what is needed is a way to allow the template to requery the data or to query a second data source as part of merging the data.
The mail merge helper uses data from a text file or WORD table in much the same way as data from an Excel spreadsheet is used. These types of data files present information as a series sequential data records that are then processed in turn. The template must be set up to generate a single letter, label or address for each processed record. What is needed is way to present highly structured data in an advanced format.
The mail merge helper includes two aspects that may seem to offer such functionality. These are the catalog merge type and the ACCESS database data source. But even these are quite limited, as is shown in FIGS. 9 through 14.
A computer user may use ACCESS or similar relational database to store data, such as the data for company employees, for example. As one skilled in the art knows, such a relational database is a collection of data items organized as a set of formally-described tables from which data can be accessed or reassembled in many different ways without having to reorganize the database tables.
FIG. 9 shows two tables set up in ACCESS for storing information about employees. The design for the Employees table 90 states that it stores the employee's ID, name, start date and job title. The design of the ContactInfo table 92 states that it stores the address, city, state, ZIP and phone for a contact. Employees may have several contacts. For example, an employee may have a home address, a work address, and perhaps a sabbatical address. FIG. 10 displays the Employees table 100 and ContactInfo table 102 with data for two employees. Per the relationship between the tables shown in FIG. 9, the ID field of the Employees table 100 is used as the foreign key (i.e., EmployeeFK) for the ContactInfo table 102. Thus, the Steven Lieske employee has three ContactInfo records (i.e., 1, 2 and 3) and the Mike Bennett employee has two ContactInfo records (i.e., 4 and 5).
FIG. 11 illustrates a report 112 created in ACCESS that prints out the full information for both of the employees. Here one sees the employee information for each employee as well as all associated contact information. Notice that while Steven Lieske has three contact addresses, his name only appears once rather than three times. In other words, the reports displays the data for each employee in a non-repetitive, easy-to-understand format. With such a report, even if Mike Bennett had 100 contact addresses set up in the database, they would all be included in the report without any additional programming effort.
The mail merge helper in WORD supports ACCESS database data. However, it does not support even as much functionality as the report writer does in ACCESS. As a first step, FIG. 12 shows a SQL query 120 and query result 122 to gather all of the information that is found in the report shown in FIG. 11. FIG. 13 shows the mail merge helper 132 again, but this time the data source selected is the ACCESS database. As the mail merge helper does not support databases very well, one must choose a query defined in the database or else set up such a query on the fly. Here, the ListInfo query 130 from FIG. 12 is selected. Once again, only one query is allowed and it must be set up by the end user just before performing the merge. The query may not be embedded within and saved as part of the template file.
The mail merger helper 132 also shows that the merge type is “Catalog” for this example rather than form letter. This type of merge is used when information from several records is gathered together rather than each record producing a separate document. FIG. 14 shows the template 140 that may be set up and then the resulting merged document 142 that is generated by merging the results of the ACCESS query 120 with the catalog template 140. Comparing these merged results 142 with the report shown in FIG. 11, one can see the limitations of the mail merge helper. Rather than grouping the data in a logical format, the mail merge creates repetition with each row including the redundant employee name and job title.
The functionality shown in FIGS. 1 through 14 illustrate some of the shortcomings of the present art. While the WORD mail merge helper does well in producing standard form letters, envelopes and mailing labels, it does not offer various advanced features that are also lacking elsewhere in the art. Mail merge only allows for the creation of WORD documents. What is needed is a way to merge data into templates created with other desktop applications to produce other types of documents.
Mail merge only supports spreadsheets, databases, text files and WORD tables as the data source. Unfortunately, most business systems do not store their data in one of these formats. What is needed is a system that accepts structured data in other formats, such as XML data files.
Mail merge may use a query to choose the records to apply to the merge as well as its ordering. What is needed is a system that can include multiple queries and that can alternate between reporting data on a record by record basis and reporting data aggregated in a table (or with bullets or a list).